package jzbank.util;



import jzbank.config.OConnManagerment;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

@Slf4j
public class DBUtil {



    public void updateCancelBlkList(ArrayList<String> list) {
        Connection con = null;
        PreparedStatement ps = null;
        try {

            Calendar calendar = Calendar.getInstance();

            calendar.add(Calendar.DAY_OF_MONTH, -1);

            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            String previousDateString = formatter.format(calendar.getTime());

            Calendar calendar1 = Calendar.getInstance();
            calendar1.add(Calendar.MONTH, -1); // �л����ϸ���
            calendar1.set(Calendar.DAY_OF_MONTH, calendar1.getActualMaximum(Calendar.DAY_OF_MONTH));
            int year = calendar1.get(Calendar.YEAR);
            int month = calendar1.get(Calendar.MONTH) + 1; // �·ݴ�0��ʼ�������Ҫ��1
            int day = calendar1.get(Calendar.DAY_OF_MONTH);
            String lastMounthDay = year + "-" + month + "-" + day;

            con = OConnManagerment.getDB2connction();
            String sql =" UPDATE                              "+
                    "     D_BLKLIST                       "+
                    " SET                                 "+
                    "     BGNUMTYP = ?,                   "+
                    "     JIECSJ = ?                      "+
                    " WHERE                               "+
                    "     ZHANGH IN                       "+
                    " (SELECT                             "+
                    "     D_YE.ZHANGH                     "+
                    " FROM                                "+
                    "     (                               "+
                    "         SELECT DISTINCT             "+
                    "             DUIZDH,                 "+
                    "             ZHANGH                  "+
                    "         FROM                        "+
                    "             D_ZHANGQZHYEB           "+
                    "         WHERE                       "+
                    "             ZHANGH IN               "+
                    "             (                       "+
                    "                 SELECT              "+
                    "                     ZHANGH          "+
                    "                 FROM                "+
                    "                     D_BLKLIST       "+
                    "                 WHERE               "+
                    "                     BGNUMTYP = '1') "+
                    "         AND ZHANGDYF <> ?) D_YE     "+
                    " LEFT JOIN                           "+
                    "     D_DUIZDB D_D                    "+
                    " ON                                  "+
                    "     D_YE.DUIZDH = D_D.DUIZDH        "+
                    " GROUP BY                            "+
                    "     D_YE.ZHANGH                     "+
                    " HAVING                              "+
                    "     MIN(D_D.ZHUANGT) = '7' )        ";
            ps = con.prepareStatement(sql);
            ps.setString(1,"2");
            ps.setString(2,previousDateString);
            ps.setString(3,lastMounthDay);
            ps.executeUpdate();
        } catch (SQLException e) {
            log.debug("");
            throw new RuntimeException("",e);
        }
    }

    public ArrayList<HashMap<String, String>> getAllBlklist() {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            // ��ȡ��ǰ����
            Calendar calendar = Calendar.getInstance();
            // ��ȡǰһ�������
            calendar.add(Calendar.DAY_OF_MONTH, -1);
            // ��ʽ������Ϊ�ַ�����YYYY-MM-DD��
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            String previousDateString = formatter.format(calendar.getTime());

            con = OConnManagerment.getDB2connction();
            String sql ="select ZHANGH,BGNUMTYP from D_BLKLIST where (NAHSJ >= ? or JIECSJ >= ?)";
            ps = con.prepareStatement(sql);
            ps.setString(1,previousDateString);
            ps.setString(2,previousDateString);
            ResultSet resultSet = ps.executeQuery();
            ArrayList<HashMap<String, String>> hashMaps = new ArrayList<HashMap<String, String>>();

            while (resultSet.next()) {
                HashMap<String, String> resultMap = new HashMap<String, String>();
                String zhangh = resultSet.getString("ZHANGH");
                String bgnumtyp = resultSet.getString("BGNUMTYP");
                resultMap.put("zhangh", zhangh);
                resultMap.put("bgnumtyp", bgnumtyp);
                hashMaps.add(resultMap);
            }

            resultSet.close();
            return hashMaps;
        } catch (SQLException e) {
            log.debug("");
            throw new RuntimeException("",e);
        }
    }
}

